﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Text;

namespace PM.PMBusiness
{
    /// <summary>
    /// projectschedual 的摘要说明
    /// </summary>
    public class projectschedual
    {
        private const string SQL_INSERT_ITEM = "INSERT INTO Bus_NumControl VALUES( ";
        public projectschedual()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        /// <summary>
        /// 增加一项计划（第一步）
        /// </summary>
        ///<param name="model">项目计划实体类</param>
        ///<returns>影响行数</returns>
        public string Add(PM.Model.ProjectPlan model)
        {
            try
            {
                SqlParameter[] parameters = {
                    //new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@type", SqlDbType.Int,4),
					new SqlParameter("@plevel", SqlDbType.NVarChar,50),
					new SqlParameter("@ptype", SqlDbType.NVarChar,50),
					new SqlParameter("@opendate", SqlDbType.DateTime),
					new SqlParameter("@closedate", SqlDbType.DateTime),
					new SqlParameter("@builtdate", SqlDbType.DateTime),
					new SqlParameter("@pstatus", SqlDbType.NVarChar,20),
					new SqlParameter("@status", SqlDbType.SmallInt,2),
					new SqlParameter("@operate", SqlDbType.NVarChar,50),
					new SqlParameter("@remark", SqlDbType.NVarChar,400),
                    new SqlParameter("@ReturnValue", SqlDbType.NVarChar,50)};
                //parameters[0].Value = model.id;
                parameters[0].Value = model.type;
                parameters[1].Value = model.plevel;
                parameters[2].Value = model.ptype;
                parameters[3].Value = model.opendate;
                parameters[4].Value = model.closedate;
                parameters[5].Value = model.builtdate;
                parameters[6].Value = model.pstatus;
                parameters[7].Value = model.status;
                parameters[8].Value = model.operate;
                parameters[9].Value = model.remark;
                parameters[10].Direction = ParameterDirection.Output;

                int rowsAffected = 0;
                return PM.Code.DAL.DbHelper.RunProcedure("Pdmis_ProjectPlanAdd", parameters, out rowsAffected).ToString();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 创建div信息框
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public string getProjectPlan(string date)
        {
            //新开启项目:项目开始时间>当前时间，即项目还没有开启
            //过期项目:项目的结束时间<当前时间
            //正在执行的项目:项目开始时间<当前时间<项目结束时间
            StringBuilder sqlstr = new StringBuilder("select * from (");
            StringBuilder divHtml = new StringBuilder();
            sqlstr.AppendFormat("select top 3 *,1 as tag from Bus_ProjectPlan where opendate>'{0}'", date);
            sqlstr.Append(" and pstatus='已完成' order by opendate) as a union select * from (");
            sqlstr.AppendFormat("select top 3 *,3 as tag from Bus_ProjectPlan where opendate<='{0}' and closedate>='{0}'", date);
            sqlstr.Append(" and pstatus='已完成' order by opendate desc) as b union select * from (");
            sqlstr.AppendFormat("select top 3 *,2 as tag from Bus_ProjectPlan where closedate<'{0}'", date);
            sqlstr.Append(" and pstatus='已完成'  order by closedate desc) as c order by tag");

            SqlDataReader result = PM.Code.DAL.DbHelper.ExecuteDataReaderBySql(sqlstr.ToString());
            if (result.HasRows)
            {
                //绘制html
                createDiv(ref divHtml,result);
            }
            divHtml.Append("?无任何数据?无任何数据?无任何数据");
            return divHtml.ToString();
        }

        private void createDiv(ref StringBuilder divHtml,SqlDataReader aresult)
        {
            int tag = 1;//标记
            int count1 = 0;
            int count2 = 0;
            int count3 = 0;
            while (aresult.Read())
            {
                if (aresult["tag"].ToString().Trim() != tag.ToString())
                {
                    tag = Int16.Parse(aresult["tag"].ToString().Trim());
                    if (aresult["tag"].ToString().Trim() == "2" && count1 == 0)
                    {
                        divHtml.Append("<h2>无任何数据</h2>");
                    }
                    if (aresult["tag"].ToString().Trim() == "3")
                    {
                        if (count1 == 0 && count2 == 0)
                            divHtml.Append("<h2>无任何数据</h2>?<h2>无任何数据</h2>");
                        if (count1 != 0 && count2 == 0)
                            divHtml.Append("?<h2>无任何数据</h2>");
                    }
                    divHtml.Append("?");
                }
                if (aresult["tag"].ToString().Trim() == "1")
                {
                    divHtml.AppendFormat("<h2>{0}于{1}开启</h2>", aresult["plancode"].ToString(), aresult["opendate"].ToString());
                    count1++;
                }
                else if (aresult["tag"].ToString().Trim() == "2")
                {
                    divHtml.AppendFormat("<h2>{0}于{1}关闭</h2>", aresult["plancode"].ToString(), aresult["closedate"].ToString());
                    count2++;
                }
                else
                {
                    divHtml.AppendFormat("<h2>{0}正在执行中……</h2>", aresult["plancode"].ToString());
                    count3++;
                }
            }
            aresult.Close();
        }
        /// <summary>
        /// 插入控制数量
        /// </summary>
        /// <param name="temp">GridView控件</param>
        /// <param name="plancode">计划编号</param>
        public void AddNumber(GridView temp,string plancode)
        {
            PM.Model.NumControl nc = new PM.Model.NumControl();
            StringBuilder sqlstr = new StringBuilder();
            SqlParameter[] itemParms;
            int i = 0;
            using (SqlConnection connection = new SqlConnection(PM.Code.DAL.DbHelper.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                foreach (GridViewRow gvrow in temp.Rows)
                {
                    nc.collegeid = gvrow.Cells[0].Text.Trim();
                    nc.number = Int32.Parse(((TextBox)(gvrow.FindControl("TextBox1"))).Text.Trim());
                    nc.plancode = plancode;
                    nc.builddate = DateTime.Now;

                    sqlstr.Append(SQL_INSERT_ITEM).Append(" @collegeid").Append(i).Append(", @plancode").Append(i).Append(", @number").Append(i).Append(", @builddate").Append(i).Append("); ");
                    itemParms = CreateParameters(i);//创建参数
                    itemParms[0].Value = nc.collegeid;
                    itemParms[1].Value = nc.plancode;
                    itemParms[2].Value = nc.number;
                    itemParms[3].Value = nc.builddate;
                    //传入参数到cmd中
                    foreach (SqlParameter parm in itemParms)
                        cmd.Parameters.Add(parm);
                    i++;
                }
                sqlstr.Append(" update Bus_ProjectPlan set pstatus='已完成' where plancode=@plancode0");
                connection.Open();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlstr.ToString();
                try
                {
                    cmd.ExecuteNonQuery();
                    //成功则提交操作
                    tx.Commit();
                }
                catch
                {
                    //失败回滚操作
                    tx.Rollback();
                }
            }
        }
        /// <summary>
        /// 创建cmd参数
        /// </summary>
        /// <param name="i">编号</param>
        /// <returns>返回参数数组</returns>
        private static SqlParameter[] CreateParameters(int i)
        {
            SqlParameter[] parms = new SqlParameter[] {
					new SqlParameter("@collegeid" + i, SqlDbType.NVarChar,50),
					new SqlParameter("@plancode"+i, SqlDbType.NVarChar, 50),
					new SqlParameter("@number"+i, SqlDbType.Int, 4),
					new SqlParameter("@builddate"+i, SqlDbType.DateTime)};
            return parms;
        }
        /// <summary>
        /// 更新学院控制数量
        /// </summary>
        /// <param name="i">要更新的编号</param>
        /// <param name="id">要更新的数量</param>
        /// <returns>返回错误行数</returns>
        public int Update_Number(int id,int number)
        {
            try
            {
                SqlParameter[] NumSqlParameter ={ 
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@number",SqlDbType.Int)};
                NumSqlParameter[0].Value = id;
                NumSqlParameter[1].Value = number;
                StringBuilder sqlstr = new StringBuilder("update Bus_NumControl set number=@number where id=@id");
                sqlstr.Append(";select @@IDENTITY");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), NumSqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return 0;
        }
        /// <summary>
        /// 更新项目计划的状态（中断）
        /// </summary>
        /// <param name="planid">唯一编号id</param>
        /// <returns>返回整数值</returns>
        public int Update_PlanStatus(int planid, string pstatus)
        {
            try
            {
                SqlParameter[] PlanParameter ={ 
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@pstatus",SqlDbType.NVarChar)};
                PlanParameter[0].Value = planid;
                PlanParameter[1].Value = pstatus;
                StringBuilder sqlstr = new StringBuilder("update Bus_ProjectPlan set pstatus=@pstatus  where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), PlanParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return 0;
        }
        /// <summary>
        /// 生成div信息框并绑定两个ddl
        /// </summary>
        /// <param name="ddl1">要绑定的下拉框1</param>
        /// <param name="ddl2">要绑定的下拉框2</param>
        /// <param name="con">是否生成警告</param>
        /// <returns>返回html源</returns>
        public string GetData(DropDownList ddl1,DropDownList ddl2,bool con)
        {
            SqlDataReader reader = null;
            StringBuilder sqlstr = new StringBuilder();
            StringBuilder divshtml = new StringBuilder();
            sqlstr.Append("select * from View_ProjectPlan ");
            sqlstr.Append("where opendate>'").Append(DateTime.Now.ToString()).Append("' ");
            divshtml.Append("<div class=\"bucket_content_undone\">");
            if (con)
            {
                divshtml.Append("<div>请您继续第二部操作！</div>");
            }
            try
            {
                reader = PM.Code.DAL.DbHelper.ExecuteDataReaderBySql(sqlstr.ToString());
                if (reader.HasRows)
                {
                    //创建div
                    while (reader.Read())
                    {
                        if (reader[8].ToString().Trim() == "未完成")
                        {
                            ddl1.Items.Add(new ListItem(reader[1].ToString(), reader[1].ToString()));//增加item
                            //绘制div
                            divshtml.Append("<div class=\"undone_header\">");
                            divshtml.Append("<span><img src=\"../js/themes/default/images/layout_button_up.gif\" ");
                            divshtml.Append("title=\"点击收起\" alt=\"点击收起\" onclick=\"toggle_collapse(this);\" /></span>");
                            divshtml.Append("<h2>编号为").Append(reader[1].ToString()).Append("的计划未完成！");
                            divshtml.Append("[建设时间:").Append(reader[7].ToString()).Append("</h2></div>");
                            divshtml.Append("<div class=\"undone_table\"><table cellpadding=\"0\" cellspacing=\"0\">");
                            divshtml.Append("<tbody><tr><td>类型:</td><td>").Append(reader[12].ToString()).Append("</td>");
                            divshtml.Append(" <td>类别:</td><td>").Append(reader[4].ToString()).Append("</td>");
                            divshtml.Append(" <td>级别:</td><td>").Append(reader[3].ToString()).Append("</td></tr>");
                            divshtml.Append("<tr><td>开启时间:</td><td colspan=\"2\">").Append(reader[5].ToString()).Append("</td>");
                            divshtml.Append("<td>关闭时间:</td><td colspan=\"2\">").Append(reader[6].ToString()).Append("</td></tr>");
                            divshtml.Append("<tr><td>备注:</td><td colspan=\"5\">").Append(reader[11].ToString()).Append("</td></tr>");
                            divshtml.Append("</tbody></table></div>");
                        }
                        else
                            ddl2.Items.Add(new ListItem(reader[1].ToString(), reader[1].ToString()));//增加item
                    }
                }
                else
                    divshtml.Append("无任何信息！");
                divshtml.Append("</div>");
                if (reader != null)
                {
                    reader.Close();
                }
                return divshtml.ToString();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            return "<div>对不起，系统出错！请联系管理员。</div>";
        }
        /// <summary>
        /// 更新项目计划
        /// </summary>
        /// <param name="tableid">唯一编号id</param>
        /// <param name="opendate">开启时间</param>
        /// <param name="closedate">结束时间</param>
        /// <returns>返回整数值</returns>
        public int Update_ProjectPlan(int tableid,string opendate,string closedate)
        {
            try
            {
                SqlParameter[] PlanParameter ={ 
                new SqlParameter("@id",SqlDbType.Int),
                new SqlParameter("@opendate",SqlDbType.DateTime),
                new SqlParameter("@closedate",SqlDbType.DateTime)};
                PlanParameter[0].Value = tableid;
                PlanParameter[1].Value = opendate;
                PlanParameter[2].Value = closedate;
                StringBuilder sqlstr = new StringBuilder("update Bus_ProjectPlan set opendate=@opendate,closedate=@closedate where id=@id");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), PlanParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return 0;
        }
        /// <summary>
        ///删除计划
        /// </summary>
        /// <param name="id"> 唯一码</param>
        /// <returns>返回影响的行数</returns>
        public int Delete_ProjectPlan(int id)
        {
            try
            {
                SqlParameter[] SqlParameter ={ 
                new SqlParameter("@id",id)};
                StringBuilder sqlstr = new StringBuilder("delete Bus_ProjectPlan");
                sqlstr.Append(" where id=@id");
                sqlstr.Append(";select @@ROWCOUNT");
                return PM.Code.DAL.DbHelper.ExecuteSql(sqlstr.ToString(), SqlParameter);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return 0;
        }
        /// <summary>
        /// 检测项目计划的合法性，即一种类型的项目的开启时间不能重叠
        /// </summary>
        /// <param name="id">唯一编号</param>
        /// <param name="type">类型</param>
        /// <param name="opentime">开启时间</param>
        /// <param name="closetime">关闭时间</param>
        /// <returns></returns>
        public string PlanCheck(int id,string type,DateTime opentime,DateTime closetime)
        {
            try
            {
                SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4),
					new SqlParameter("@type", SqlDbType.NVarChar,50),
					new SqlParameter("@opendate", SqlDbType.DateTime),
					new SqlParameter("@closedate", SqlDbType.DateTime),
                    new SqlParameter("@ReturnValue", SqlDbType.NVarChar,50)};
                parameters[0].Value = id;
                parameters[1].Value = type;
                parameters[2].Value = opentime;
                parameters[3].Value = closetime;
                parameters[4].Direction = ParameterDirection.Output;

                int rowsAffected = 0;
                return PM.Code.DAL.DbHelper.RunProcedure("Pdmis_PlanCheck", parameters, out rowsAffected).ToString();
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
    }
}
